/*
 * Copyright (C), 2013-2018, 上汽集团
 * FileName: QuickTranQuickWinServiceImpl.java
 * Author:   raolesong
 * Date:     2018年8月18日 上午10:51:44
 * Description: //模块目的、功能描述      
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package com.jinznet.transit.service.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.alibaba.fastjson.JSON;
import com.jinznet.transit.controller.entity.CarOwner;
import com.jinznet.transit.controller.entity.SaleMsg;
import com.jinznet.transit.controller.entity.SalePrize;
import com.jinznet.transit.controller.entity.SalePrizeCondition;
import com.jinznet.transit.service.QuickTranQuickWinService;

import org.slf4j.Logger;

/**
 * 2018年8月18日 上午10:51:44
 */
@Service
public class QuickTranQuickWinServiceImpl implements QuickTranQuickWinService {
	
	private Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public CarOwner saveCarOwnerInfo(final CarOwner vo) {
		logger.info("saveCarOwnerInfo:"+JSON.toJSONString(vo));
		KeyHolder keyHolder = new GeneratedKeyHolder();

		final String sql = "insert into t_car_owner(open_id,name,phone,vin,buy_car_time,create_time) values(?,?,?,?,?,now())";
		
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setString(1, vo.getOpenId());
				ps.setString(2, vo.getName());
				ps.setString(3, vo.getPhone());
				ps.setString(4, vo.getVin());
				ps.setDate(5, new Date(vo.getBuyCarTime().getTime()));
				return ps;
			}
		}, keyHolder);

		vo.setId(keyHolder.getKey().intValue());
		return vo;
	}

	@Override
	public boolean saveSaleMsg(final SaleMsg vo) {
		logger.info("saveSaleMsg:"+JSON.toJSONString(vo));
		KeyHolder keyHolder = new GeneratedKeyHolder();

		final String sql = "insert into t_sale_msg(open_id,store_name,store_province,store_city,sale_name,sale_phone,"
				+ "recommend_car_name,recommend_car_phone,recommend_car_vin,create_time) values(?,?,?,?,?,?,?,?,?,now())";
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setString(1, vo.getOpenId());
				ps.setString(2, vo.getStoreName());
				ps.setString(3, vo.getStoreProvince());
				ps.setString(4, vo.getStoreCity());
				ps.setString(5, vo.getSaleName());
				ps.setString(6, vo.getSalePhone());
				ps.setString(7, vo.getRecommendCarName());
				ps.setString(8, vo.getRecommendCarPhone());
				ps.setString(9, vo.getRecommendCarVin());
				return ps;
			}
		}, keyHolder);
		
		vo.setId(keyHolder.getKey().intValue());
		
		addSalePrizeCondition(vo.getOpenId());
		logger.info("===给销售添加抽奖机会==={}"+vo.getOpenId());
		return true;
	}

	@Override
	public SalePrize saveSalePrize(final SalePrize vo) {
		KeyHolder keyHolder = new GeneratedKeyHolder();

		final String sql = "insert into t_sale_prize(sale_open_id,sale_name,store_name,prize_des,create_time)"
				+ " values(?,?,?,?,now())";
		
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setString(1, vo.getSaleOpenId());
				ps.setString(2, vo.getSaleName());
				ps.setString(3, vo.getStoreName());
				ps.setString(4, vo.getPrizeDes());
				return ps;
			}
		}, keyHolder);

		vo.setId(keyHolder.getKey().intValue());
		return vo;
	}

	@Override
	public void updateSalePrizeCount(String openId) {
		final String sql = "update t_sale_prize set recommend_count=recommend_count+1 where sale_open_id='"+openId+"'";
		jdbcTemplate.execute(sql);
	}
	
	
	@Override
	public List<SalePrize> getSalePrizeList(String openId,String des,String name) {
		final List<SalePrize> salePrizeList = new ArrayList<SalePrize>();
		StringBuffer sql = new StringBuffer("select sale_open_id,sale_name,store_name,prize_des,t2.luck_count from t_sale_prize t1,t_sale_prize_condition t2"
				+ " where t1.sale_open_id=t2.open_id ");
		if(!StringUtils.isEmpty(openId)){
			sql.append(" and sale_open_id='"+openId+"'");
		}
		if(!StringUtils.isEmpty(des)){
			sql.append(" and prize_des='"+des+"'");
		}
		if(!StringUtils.isEmpty(name)){
			sql.append(" and sale_name like '%"+name+"%'");
		}
		
		jdbcTemplate.query(sql.toString(), new ResultSetExtractor<Object>(){

			@Override
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				SalePrize vo = null;
				while(rs.next()){
					vo = new SalePrize();
					vo.setSaleOpenId(rs.getString(1));
					vo.setSaleName(rs.getString(2));
					vo.setStoreName(rs.getString(3));
					vo.setPrizeDes(rs.getString(4));
					vo.setRecommendCount(rs.getInt(5));
					salePrizeList.add(vo);
				}
				return null;
			}
			
		});
		return salePrizeList;
	}

	/**
	 * 未中奖也生成一条中奖纪录
	 * prizeDes:奖项
	 */
	@Override
	public boolean doLuckPrize(String openId,String prizeDes) {
		
		int result = 0;
		String sql = "";
		if(! "0".equals(prizeDes)){
			sql = "update t_prize_item set available_num=available_num-1,use_num=use_num+1 where prize_des='"+prizeDes+"'"
					+ " and available_num>0 ";
			result = jdbcTemplate.update(sql);
			
			if(result>0){
				logger.info(openId+","+" 已中奖=====");
				//先删除未中奖纪录
				sql = "delete from t_sale_prize where sale_open_id='"+openId+"' and prize_des=0 " ;
				jdbcTemplate.update(sql);
			
				SaleMsg saleMsg = getSaleMsgByOpenId(openId);
				SalePrize vo = new SalePrize();
				vo.setPrizeDes(prizeDes);
				vo.setSaleName(saleMsg.getSaleName());
				vo.setSaleOpenId(openId);
				vo.setStoreName(saleMsg.getStoreName());
				saveSalePrize(vo);
			}
		}else{
			SaleMsg saleMsg = getSaleMsgByOpenId(openId);
			SalePrize vo = new SalePrize();
			vo.setPrizeDes(prizeDes);
			vo.setSaleName(saleMsg.getSaleName());
			vo.setSaleOpenId(openId);
			vo.setStoreName(saleMsg.getStoreName());
			saveSalePrize(vo);
			logger.info(openId+","+" 插入0元记录=====");
		}
		return result==0 ? false : true;
	}

	@Override
	public boolean addSalePrizeCondition(final String openId) {
		final String sql = "update t_sale_prize_condition set luck_count=luck_count+1"
				+ ",available_num=available_num+1 where open_id='"+openId+"'";
				
		int result = jdbcTemplate.update(sql);
		if(result<=0){
			
			KeyHolder keyHolder = new GeneratedKeyHolder();
			final String newSql = "insert into t_sale_prize_condition(open_id,luck_count,available_num,use_num)"
					+ " values(?,?,?,?)";
			
			jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(newSql, PreparedStatement.RETURN_GENERATED_KEYS);
					ps.setString(1, openId);
					ps.setInt(2, 1);
					ps.setInt(3, 1);
					ps.setInt(4,0);
					return ps;
				}
			}, keyHolder);
		}
		return true;
	}

	@Override
	public boolean reduceSalePrizeCondition(String openId) {
		final String sql = "update t_sale_prize_condition set use_num=use_num+1"
				+ ",available_num=available_num-1 where open_id='"+openId+"'";
		jdbcTemplate.update(sql);
		return true;
	}

	@Override
	public SalePrizeCondition getSalePrizeCondition(String openId) {
		final String sql = "select id,open_id,luck_count,available_num,use_num from t_sale_prize_condition where open_id='"+openId+"'";
		
		return jdbcTemplate.query(sql, new ResultSetExtractor<SalePrizeCondition>(){

			@Override
			public SalePrizeCondition extractData(ResultSet rs) throws SQLException, DataAccessException {
				SalePrizeCondition vo = null;
				if(rs.next()){
					vo = new SalePrizeCondition();
					vo.setId(rs.getLong(1));
					vo.setOpen_id(rs.getString(2));
					vo.setLuck_count(rs.getInt(3));
					vo.setAvailable_num(rs.getInt(4));
					vo.setUse_num(rs.getInt(5));
				}
				return vo;
			}
			
		});
	}
	
	
	public SaleMsg getSaleMsgByOpenId(String openId) {
		final String sql = "select store_name,sale_name,store_province,store_city,sale_phone from t_sale_msg "
				+ "where id=(select max(id) from t_sale_msg where open_id='"+openId+"')";
		
		return jdbcTemplate.query(sql, new ResultSetExtractor<SaleMsg>(){

			@Override
			public SaleMsg extractData(ResultSet rs) throws SQLException, DataAccessException {
				SaleMsg vo = null;
				if(rs.next()){
					vo = new SaleMsg();
					vo.setStoreName(rs.getString(1));
					vo.setSaleName(rs.getString(2));
					vo.setStoreProvince(rs.getString(3));
					vo.setStoreCity(rs.getString(4));
					vo.setSalePhone(rs.getString(5));
				}
				return vo;
			}
		});
	}

	@Override
	public void setSalePrizeAddress(String openId, String prizeDes,
			String province, String city, String address) {
		String sql = "update t_sale_prize set province='"+province+"',city='"+city+"'"
				+ ", address='"+address+"'  where sale_open_id='"+openId+"' and prize_des='"+prizeDes+"'" ;
		jdbcTemplate.execute(sql);
		logger.info(openId+","+prizeDes+" 已设置中奖收货地址========");
	}
	
}
